package com.usermanage.Mapping;

import com.usermanage.beanParam.GroupBean;
import com.usermanage.beanParam.UserBean;
import com.usermanage.dataDao.JpaDao;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * Created by  on 2018/9/3
 */
@Repository("userMapping")
public interface UserMapping extends JpaRepository<UserBean, Integer> {
    /**
     * 根据邮箱得到用户在几个组织中
     * @param email
     * @return
     */
//    @Query(value = "SELECT count(1) from u_user u LEFT JOIN u_group_member gm ON u.id = gm.user_id WHERE u.user_email = :email",nativeQuery = true)
//    @Query(value = "SELECT u.*,gm.group_id,gm.top_group_id,g.parent_id,g.group_uuid,gm.company_id from u_user u LEFT JOIN u_group_member gm ON u.id = gm.user_id LEFT JOIN u_group g ON gm.group_id = g.id WHERE u.user_email = :email",nativeQuery = true)
    @Query(value = "SELECT u.*,gm.group_id,uc.type from u_user u LEFT JOIN u_group_member gm ON u.id = gm.user_id LEFT JOIN u_company uc ON u.company_id = uc.id  WHERE u.user_email = :email and u.user_state='11'",nativeQuery = true)
    List<Map> findUserInfoByEmail(@Param("email") String email);


    @Query(value = "SELECT * FROM u_user WHERE user_name LIKE CONCAT('%',:name,'%') AND company_id = :companyId",nativeQuery = true)
    List<Map> findUserByUserNameAndCompanyId(@Param("name") String name,@Param("companyId") String companyId);

    @Query(value = "SELECT * FROM u_user WHERE user_state = '11'",nativeQuery = true)
    List<UserBean> findAllUser();

    @Query(value = "SELECT uu.* FROM u_group_member ugm left join u_user uu on uu.id=ugm.user_id WHERE user_state = '11'and ugm.group_id = :groupId",nativeQuery = true)
    List<UserBean> findAllUserByGroupId(@Param("groupId") String groupId);

    @Query(value = "SELECT * FROM u_user WHERE company_id = :companyId AND user_state = '11'",nativeQuery = true)
    List<Map> findUserByCompanyId(@Param("companyId") String companyId);

    @Query(value = "SELECT * FROM u_user WHERE user_name LIKE CONCAT('%',:name,'%')",nativeQuery = true)
    List<Map> findAllUserByUserName(@Param("name") String name);

    @Query(value = "SELECT u.*,uc.type from u_user u  LEFT JOIN u_company uc ON  u.company_id = uc.id where u.id = :id",nativeQuery = true)
    Map findUserDetail(@Param("id") String id);

    @Query(value = "SELECT * from u_user WHERE user_email = :email",nativeQuery = true)
    Map findUserDetailForPort(@Param("email") String email);

    @Query(value = "SELECT id from u_user WHERE user_name = :user_name and company_id = :companyId",nativeQuery = true)
    Map findUserIdForPort(@Param("user_name") String user_name,@Param("companyId") String companyId);
}
